--    Program: 		CAPPOEXT_BUYER.sql
--    Description:	Extract PO Buyer information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
   
   l_name					varchar2(20);
   l_set_of_books_id        number;
                    		  
   cursor cur_po_agents(p_sob_name varchar2) is
      select pa.* 
      from   po_agents pa,
             per_all_people_f pap,
      	     hr_organization_units hou
      where  pa.agent_id = pap.person_id
      and	 pap.business_group_id = hou.business_group_id
      and    upper(hou.name) = 'BG_'||upper(substr(p_sob_name, -4));

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_BUYER.dat';
   l_utl_file := utl_file.fopen(l_pathname,l_filename,'w',32767);
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_name := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_name);  
   exception
      when others then
        dbms_output.put_line(l_name||': set of books name not found');
   end; 

   for cur in cur_po_agents(l_name) loop   	
    
	 -- output data
     utl_file.put_line( l_utl_file, 
	   cur.AGENT_ID|| chr(9) ||
	   cur.LAST_UPDATE_DATE || chr(9) ||
	   cur.last_updated_by|| chr(9) ||
	   cur.LAST_UPDATE_LOGIN  || chr(9) ||
	   cur.CREATION_DATE || chr(9) ||
	   cur.created_by|| chr(9) ||
	   cur.LOCATION_ID|| chr(9) ||
	   cur.CATEGORY_ID|| chr(9) ||
	   cur.AUTHORIZATION_LIMIT|| chr(9) ||
	   cur.START_DATE_ACTIVE|| chr(9) ||
	   cur.END_DATE_ACTIVE|| chr(9) ||
	   cur.ATTRIBUTE_CATEGORY|| chr(9) ||
	   cur.ATTRIBUTE1|| chr(9) ||
	   cur.ATTRIBUTE2|| chr(9) ||
	   cur.ATTRIBUTE3|| chr(9) ||
	   cur.ATTRIBUTE4|| chr(9) ||
	   cur.ATTRIBUTE5|| chr(9) ||
	   cur.ATTRIBUTE6|| chr(9) ||
	   cur.ATTRIBUTE7|| chr(9) ||
	   cur.ATTRIBUTE8|| chr(9) ||
	   cur.ATTRIBUTE9|| chr(9) ||
	   cur.ATTRIBUTE10|| chr(9) ||
	   cur.ATTRIBUTE11|| chr(9) ||
	   cur.ATTRIBUTE12|| chr(9) ||
	   cur.ATTRIBUTE13|| chr(9) ||
	   cur.ATTRIBUTE14|| chr(9) ||
	   cur.ATTRIBUTE15|| chr(9) ||
	   cur.REQUEST_ID || chr(9) ||
	   cur.PROGRAM_APPLICATION_ID|| chr(9) ||
	   cur.PROGRAM_ID ||chr(9)||
	   cur.PROGRAM_UPDATE_DATE 
	 );
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'Po Buyer not exists';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_BUYER', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg ); 
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
